package com.xinzhitong.www.mapper;

import com.xinzhitong.www.enumeration.OrderType;
import com.xinzhitong.www.pojo.Post;
import com.xinzhitong.www.provider.SelectSQLProvider;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

/**
 * post表相关操作
 *
 * @author 徐鑫 钟伟良 魏骆淳
 */
@Repository
public interface PostMapper {

    @Select("select * from post_summary where companyId=#{companyId}")
    List<Map<String, Object>> selectPostsByCompanyId(@Param("companyId") Integer companyId);

    @Select("select * from post_info where id=#{id}")
    Map<String, Object> selectPostById(@Param("id") Integer id);

    @Insert("insert into user_enroll_post(username, post_id) values(#{username}, #{postId})")
    Integer insertUserEnrollPost(String username, Integer postId);

    @Select("select count(*) from user_enroll_post where username=#{username} and post_id=#{postId}")
    Integer selectCountUserEnrollPost(String username, Integer postId);

    @SelectProvider(type = SelectSQLProvider.class, method = "getSQLOfSelectByConditionsWithKeyWord")
    List<Map<String, Object>> selectPostsByConditions(
            @Param("table") String table,
            @Param("selectFields") String[] selectFields,
            @Param("keyWord") String keyWord,
            @Param("orderField") String orderField,
            @Param("orderType") OrderType orderType,
            @Param("conditions") Map<String, Object> conditions
    );

    @Select("select remainQuota from post_remain_quota where id=#{id}")
    Integer selectRemainQuotaOfPostById(@Param("id") Integer id);

    @Select("SELECT column_type " +
            "FROM information_schema.COLUMNS " +
            "WHERE TABLE_SCHEMA=\"xinzhitong\" " +
            "AND DATA_TYPE='enum' " +
            "AND table_name=\"post\" " +
            "AND column_name=\"edu_background\"")
    String selectEnumOfEdu();

    @Select("select count(0)\n" +
            "FROM\n" +
            "(SELECT * from post where company_id=#{companyId} and id not in (\n" +
            "\tselect id from consign\n" +
            "))tmp;")
    Integer countPostOfTheCompany(@Param("companyId") Integer companyId);

    @Select("SELECT\n" +
            "count(0)\n" +
            "FROM\n" +
            "company\n" +
            "INNER JOIN post ON post.company_id = company.id\n" +
            "INNER JOIN user_enroll_post ON user_enroll_post.post_id = post.id\n" +
            "where company.id=#{companyId}")
    Integer countEnrollUserOfPostOfTheCompany(@Param("companyId") Integer companyId);

    @Select("SELECT id,name,count,requirePopulation,publishTime,state" +
            " FROM post_general where state LIKE #{state} and name LIKE #{name} and username=#{username}")
    List<Map<String, Object>> selectPostGeneral(String name, String state, String username);

    @Select("SELECT username,date,realname,id,contact,username FROM user_enroll_post_condition where postId=#{postId}")
    List<Map<String, Object>> SelectPostCondition(Integer postId);

    @Select("SELECT last_login_time FROM user where username=#{username}")
    Map<String, Object> SelectLastTime(String username);

    @Select("SELECT * from post_new_enroll where date<=#{time} and companyUsername=#{username}")
    List<Map<String, Object>> SelectNewPost(Timestamp time, String username);

    @Update("Update post SET state='已下架' where id=#{id}")
    Integer updateStateOfPost(Integer id);

    @Delete("Delete from post  where id=#{id}")
    Integer deletePost(Integer id);

    @Select("SELECT positionId,name FROM position_get where professionId=#{professionId}")
    List<Map<String, Object>> getPosition(Integer id);

    @Select("select postName, companyName, contact from post_information_view where postId = #{postId}")
    Map<String, Object> selectPostInformation(@Param("postId") Integer postId);

    @Select("select enrollCount, clickCount, collectCount, shareCount from post_index_view where postId = #{postId}")
    Map<String, Object> selectPostIndex(@Param("postId") Integer postId);

    @Select("select companyId, companyName from company_by_username_view where username = #{username}")
    Map<String, Object> selectCompanyByUsername(String username);

    @Select("select name from position where id = #{positionId}")
    String selectPositionNameByPositionId(Integer positionId);

    @Select("select name from profession_name_by_position_id_view where id = #{positionId}")
    String selectProfessionNameByPositionId(Integer positionId);

    @Select("select * from post_or_consign_total_information_view where postId = #{postId}")
    Map<String, Object> selectPostTotalInformation(Integer postId);

    @Insert("insert into post(company_id, position_id, name, require_population, contact, address, base_salary, " +
            "max_salary, min_salary, pay_salary_date, pay_type, salary_other_notice, live_notice, food_notice, " +
            "social_security_notice, live_food_other_notice, job_text, job_time_notice, job_way, job_other_notice, " +
            "sex_request, max_age_request, min_age_request, edu_background, min_exp_request, max_exp_request, " +
            "credential_request, employ_other_notice, state) " +
            "values (#{companyId}, #{positionId}, #{name}, #{requirePopulation}, #{contact}, #{address}, " +
            "#{baseSalary}, #{maxSalary}, #{minSalary}, #{paySalaryDate}, #{payType}, #{salaryOtherNotice}, " +
            "#{liveNotice}, #{foodNotice}, #{socialSecurityNotice}, #{liveFoodOtherNotice}, #{jobText}, " +
            "#{jobTimeNotice}, #{jobWay}, #{jobOtherNotice}, #{sexRequest}, #{maxAgeRequest}, #{minAgeRequest}, " +
            "#{eduBackground}, #{minExpRequest}, #{maxExpRequest}, #{credentialRequest}, #{employOtherNotice}, " +
            "#{state})")
    void insertPostInformation(
            Integer companyId,
            Integer positionId,
            Integer requirePopulation,
            String contact,
            String address,
            double baseSalary,
            double maxSalary,
            double minSalary,
            Integer paySalaryDate,
            String payType,
            String salaryOtherNotice,
            String liveNotice,
            String foodNotice,
            String socialSecurityNotice,
            String liveFoodOtherNotice,
            String jobText,
            String jobTimeNotice,
            String jobWay,
            String jobOtherNotice,
            Boolean sexRequest,
            Integer maxAgeRequest,
            Integer minAgeRequest,
            String eduBackground,
            Integer minExpRequest,
            Integer maxExpRequest,
            String credentialRequest,
            String employOtherNotice,
            String name,
            String state
    );

    @Update("update post set name = #{name}, position_id = #{positionId}, require_population = #{requirePopulation}, " +
            "contact = #{contact}, address = #{address}, base_salary = #{baseSalary}, max_salary = #{maxSalary}, " +
            "min_salary = #{minSalary}, pay_salary_date = #{paySalaryDate}, pay_type = #{payType}, " +
            "salary_other_notice = #{salaryOtherNotice}, live_notice = #{liveNotice}, food_notice = #{foodNotice}, " +
            "social_security_notice = #{socialSecurityNotice}, live_food_other_notice = #{liveFoodOtherNotice}, " +
            "job_text = #{jobText}, job_time_notice = #{jobTimeNotice}, job_way = #{jobWay}, " +
            "job_other_notice = #{jobOtherNotice}, sex_request = #{sexRequest}, max_age_request = #{maxAgeRequest}, " +
            "min_age_request = #{minAgeRequest}, edu_background = #{eduBackground}, " +
            "min_exp_request = #{minExpRequest}, max_exp_request = #{maxExpRequest}, " +
            "credential_request = #{credentialRequest}, employ_other_notice = #{employOtherNotice} " +
            "where id = #{postId}")
    void updatePost(
            Integer postId,
            Integer positionId,
            Integer requirePopulation,
            String contact,
            String address,
            double baseSalary,
            double maxSalary,
            double minSalary,
            Integer paySalaryDate,
            String payType,
            String salaryOtherNotice,
            String liveNotice,
            String foodNotice,
            String socialSecurityNotice,
            String liveFoodOtherNotice,
            String jobText,
            String jobTimeNotice,
            String jobWay,
            String jobOtherNotice,
            Boolean sexRequest,
            Integer maxAgeRequest,
            Integer minAgeRequest,
            String eduBackground,
            Integer minExpRequest,
            Integer maxExpRequest,
            String credentialRequest,
            String employOtherNotice,
            String name
    );

    @Select("select postName, requirePopulation, enrollPopulation, state, releaseTime from recent_editor_post " +
            "where username = #{username}")
    List<Map<String, Object>> selectRecentEditorPost(String username);

    @Update("update post set state = '已发布' where id = #{postId} and state = '待审核'")
    void updatePostStateRelease(Integer postId);

    @Update("update post set state = '未通过' where id = #{postId} and state = '待审核'")
    void updatePostStateReject(Integer postId);

    @Select("select * from post where id = #{postId}")
    Post selectPost(Integer postId);

    @Select("select name,picture,companyName,publishTime,id,count,state from post_info where " +
            "professionId LIKE #{professionId} and name LIKE #{name}")
    List<Map<String, Object>> selectAdminPostGeneral1(String name, Integer professionId);

    @Select("select name,picture,companyName,publishTime,id,count,state from post_info where " +
            " name LIKE #{name}")
    List<Map<String, Object>> selectAdminPostGeneral2(String name);

    @Select("select count from post_enroll_count_view where postId=#{postId}")
    Integer selectAdminPostEnrollCount(Integer postId);

}
